let server = require("express")();
let expressStatic = require("express-static");
let mysql = require("mysql");
let port = 8081;
// 规划一个数据库连接
let sql = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : '123456',
  database : 'sms',
  timezone : "08:00"
});

sql.connect();
server.get("/getstudentList",(request,response)=>{
    // response.send("hello")
    sql.query(`SELECT * FROM students`,(error,data)=>{
        if(error){
            console.log(error);
            response.send("error");    
        }
        else{
            response.send(JSON.stringify(data))
        }
    })
})

server.get("/deleteStudent",(request,response)=>{
	sql.query(`DELETE FROM students WHERE id=${request.query.id}`,(error,data)=>{
		if(error){
			console.log(error)
			response.send("error")
		}
		else{
			response.send("success")
		}
	})
})

server.get("/addStudent",(request,response)=>{
	let p = request.query;
	sql.query(`INSERT INTO students (name,age,sex,city,joinDate) VALUES ("${p.name}","${p.age}","${p.sex}","${p.city}","${p.joinDate}")`,(error,data)=>{
		if(error){
			console.log(error)
			response.send("error")
		}
		else{
			response.send("success")
			console.log(`新增学生${p.name}操作成功`)
		}
	})
})

server.get("/updateStudent",(request,response)=>{
	let p = request.query;
	sql.query(`UPDATE students SET name="${p.name}",sex="${p.sex}",age="${p.age}",city="${p.city}",joinDate="${p.joinDate}" WHERE id="${p.id}"`,(error,data)=>{
		if(error){
			console.log(error)
			response.send("error")
		}
		else{
			response.send("success")
			console.log(`更改学生${p.name}操作成功`)
		}
	})
})

server.get("/login",(request,response)=>{
	let p = request.query;
	sql.query(`SELECT * FROM users WHERE username="${p.username}" AND password="${p.password}"`,(error,data)=>{
		if(error){
			console.log(error)
			response.send("3");
		}
		else{
			if(data.length === 0){
				response.send("2");
			}
			else{
				response.send(JSON.stringify(data));
			}
			// response.send("success")
			// console.log(data)
		}
		
	})
})

server.get("/register",(request,response)=>{
	let p = request.query;
	sql.query(`INSERT INTO users (username,name,password,usertype) VALUES ("${p.username}","${p.name}","${p.password}","${p.usertype}")`,(error,data)=>{
		if(error){
			console.log(error);
			response.send("error")
		}
		else{
			response.send("success")
			console.log(`${p.username}用户注册成功`);
			
		}
	})	
})

// 配置静态目录
server.use(expressStatic(__dirname + '/static'));
server.listen(port)
console.log(`server is running at ${port}`);
